<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

<!-- 分表模式的mapper xml -->
<mapper namespace="${m.d.pkg}.${m.d.className}" >
    <!-- 基本数据映射 -->
    <resultMap id="BaseResultMap" type="${m.e.pkg}.${m.e.className}">
        <#list m.fields as field>
        <result column="${field.columnName}" property="${field.name}" javaType="${field.javaType}" />
        </#list>
    </resultMap>


    <!-- 基本sql片段 -->
    <!-- 全部字段 -->
    <sql id="sql_all_columns_all">
        <#list m.fields as field>`${field.columnName}`<#if field?has_next>, </#if></#list>
    </sql>
    <!-- 部分字段 -->
    <sql id="sql_all_columns">
        <if test="fields == null || fields.fields == null"><include refid="sql_all_columns_all"/></if>
        <if test="fields != null">
            <foreach collection="fields.fields" item="field" separator=",">${r"${field}"}</foreach>
        </if>
    </sql>
    <!-- query生成where子句 -->
    <sql id="sql_query_where">
        <trim prefixOverrides="and">
            1 = 1
            <#list m.fields as field>
            <if test="query.${field.name} != null">and `${field.columnName}` = ${r"#{query."}${field.name}${r"}"}</if>
            <if test="query.${field.name}In != null">and `${field.columnName}` in (<foreach collection="query.${field.name}In" item="item" separator=",">${r"#{"}item${r"}"}</foreach>)</if>
            <#if field.like>
            <if test="query.${field.name}XLike != null">and `${field.columnName}` like '%${r"${query."}${field.name}${r"XLike}"}%'</if>
            <if test="query.${field.name}XLeft != null">and `${field.columnName}` like '${r"${query."}${field.name}${r"XLeft}"}%'</if>
            <if test="query.${field.name}XRight != null">and `${field.columnName}` like '%${r"${query."}${field.name}${r"XRight}"}'</if>
            </#if>
            <#if field.compare>
            <if test="query.${field.name}Gt != null">and `${field.columnName}` <![CDATA[>]]> ${r"#{query."}${field.name}${r"Gt}"}</if>
            <if test="query.${field.name}Gte != null">and `${field.columnName}` <![CDATA[>=]]> ${r"#{query."}${field.name}${r"Gte}"}</if>
            <if test="query.${field.name}Lt != null">and `${field.columnName}` <![CDATA[<]]> ${r"#{query."}${field.name}${r"Lt}"}</if>
            <if test="query.${field.name}Lte != null">and `${field.columnName}` <![CDATA[<=]]> ${r"#{query."}${field.name}${r"Lte}"}</if>
            </#if>

            </#list>
        </trim>
    </sql>
    <!-- entity生成insert字段 -->
    <sql id="sql_entity_insert">
        <trim prefixOverrides=",">
        <#list m.fields as field>
        <if test="entity.${field.name} != null">, ${r"#{"}entity.${field.name}${r"}"} </if><if test="entity.${field.name} == null">,null </if>
        </#list>
        </trim>
    </sql>
    <!-- entity生成的update字段 -->
    <sql id="sql_entity_update">
        <trim prefixOverrides=",">
        <#list m.fields as field>
        <if test="entity.${field.name} != null">, `${field.name}` = ${r"#{entity."}${field.name}${r"}"}</if>
        </#list>
        </trim>
    </sql>
    <!-- update生成的update字段 -->
    <sql id="sql_update_entity_update">
        <trim prefixOverrides=",">
        <#list m.fields as field>
        <if test="update.entity.${field.name} != null">, `${field.name}` = ${r"#{update.entity."}${field.name}${r"}"}</if>
        </#list>
        </trim>
    </sql>
    <sql id="sql_update_entity_query">
        <trim prefixOverrides="and">
            1 = 1
            <#list m.fields as field>
            <if test="update.query.${field.name} != null">and `${field.columnName}` = ${r"#{update.query."}${field.name}${r"}"}</if>
            <if test="update.query.${field.name}In != null">and `${field.columnName}` in (<foreach collection="update.query.${field.name}In" item="item" separator=",">${r"#{"}item${r"}"}</foreach>)</if>
            <#if field.like>
            <if test="update.query.${field.name}XLike != null">and `${field.columnName}` like '%${r"${update.query."}${field.name}${r"XLike}"}%'</if>
            <if test="update.query.${field.name}XLeft != null">and `${field.columnName}` like '${r"${update.query."}${field.name}${r"XLeft}"}%'</if>
            <if test="update.query.${field.name}XRight != null">and `${field.columnName}` like '%${r"${update.query."}${field.name}${r"XRight}"}'</if>
            </#if>
            <#if field.compare>
            <if test="update.query.${field.name}Gt != null">and `${field.columnName}` <![CDATA[>]]> ${r"#{update.query."}${field.name}${r"Gt}"}</if>
            <if test="update.query.${field.name}Gte != null">and `${field.columnName}` <![CDATA[>=]]> ${r"#{update.query."}${field.name}${r"Gte}"}</if>
            <if test="update.query.${field.name}Lt != null">and `${field.columnName}` <![CDATA[<]]> ${r"#{update.query."}${field.name}${r"Lt}"}</if>
            <if test="update.query.${field.name}Lte != null">and `${field.columnName}` <![CDATA[<=]]> ${r"#{update.query."}${field.name}${r"Lte}"}</if>
            </#if>

            </#list>
        </trim>
    </sql>
    <!-- 表名 -->
    <sql id="sql_table_name" >`${m.t.name}_${r"${shardSuffix}"}`</sql>


    <!-- 查询分表的全部数据 -->
    <select id="findAllWithShardSuffix" resultMap="BaseResultMap">
        select
        <include refid="sql_all_columns" ></include>
        from <include refid="sql_table_name"></include>
    </select>


    <!-- 条件查询 -->
    <select id="findByQuery" resultMap="BaseResultMap">
        select
        <include refid="sql_all_columns" ></include>
        from <include refid="sql_table_name"></include>
        <where>
            <include refid="sql_query_where"></include>
        </where>
    </select>
    <select id="findByQueryWithOrderBy" resultMap="BaseResultMap">
        select
        <include refid="sql_all_columns" ></include>
        from <include refid="sql_table_name"></include>
        <where>
            <include refid="sql_query_where"></include>
        </where>
        <if test="orderBy != null">
            order by <foreach collection="orderBy.items" item="ob" separator=","> ${r"${ob.column} ${ob.tag}"} </foreach>
        </if>
    </select>


    <!-- 查询数量 -->
    <select id="count" resultType="java.lang.Integer">
        select count(*) from <include refid="sql_table_name"></include>
        <where>
            <include refid="sql_query_where"></include>
        </where>
    </select>
    <select id="countByQuery" resultType="java.lang.Integer">
        select count(*) from <include refid="sql_table_name"></include>
        <where>
            <include refid="sql_query_where"></include>
        </where>
    </select>
    <!-- 查找在指定limit数据量条件下的count，极值 -->
    <resultMap id="countByQueryWithOrderByResultMap" type="${m.pageCountDO}">
        <result column="count" property="count" javaType="java.lang.Integer"/>
        <association property="extreme" resultMap="BaseResultMap" columnPrefix="extreme_"/>
    </resultMap>
    <select id="countByQueryWithOrderBy" resultMap="countByQueryWithOrderByResultMap">
        select count(*) as `count`, ${r"${orderBy.currentItem.extreme}"}
        from (
        select <include refid="sql_all_columns" /> from <include refid="sql_table_name"/>
        <where>
            <include refid="sql_query_where" />
            <if test="orderBy.stationaryList != null">
                <foreach collection="orderBy.stationaryList" item="item">
                    and ${r"${item.column} ${item.affirm} #{item.val}"}
                </foreach>
            </if>
        </where>
        limit ${r"${page.limit}"}
        ) as t
    </select>
    <!-- 查询极值左边的数量，和等于极值的数量 -->
    <select id="countByQueryWithExtreme" resultType="java.lang.Integer">
        select count(*) as `count` from <include refid="sql_table_name"/>
        <where>
            <include refid="sql_query_where" />
            <if test="orderBy.currentItem.val != null">
                and ${r"${orderBy.currentItem.column} ${orderBy.currentItem.negate} #{orderBy.currentItem.val}"}
            </if>
            <if test="orderBy.stationaryList != null">
                <foreach collection="orderBy.stationaryList" item="item">
                    and ${r"${item.column} ${item.affirm} #{item.val}"}
                </foreach>
            </if>
        </where>
        union
        select count(*) as `count` from <include refid="sql_table_name"/>
        <where>
            <include refid="sql_query_where" />
            <if test="orderBy.currentItem.val != null">
                and ${r"${orderBy.currentItem.column} = #{orderBy.currentItem.val}"}
            </if>
            <if test="orderBy.stationaryList != null">
                <foreach collection="orderBy.stationaryList" item="item">
                    and ${r"${item.column} ${item.affirm} #{item.val}"}
                </foreach>
            </if>
        </where>
    </select>


    <!-- 分页查询 -->
    <select id="findByAfterPage" resultMap="BaseResultMap">
        select
        <include refid="sql_all_columns" ></include>
        from <include refid="sql_table_name"></include>
        <where>
            <include refid="sql_query_where"></include>
            <if test="orderBy.firstItem.val != null">
                and ${r"${orderBy.firstItem.column} ${orderBy.firstItem.affirm} #{orderBy.firstItem.val}"}
            </if>
        </where>
        <if test="orderBy != null">
            order by <foreach collection="orderBy.items" item="ob" separator=",">${r"${ob.column} ${ob.tag}"} </foreach>
        </if>
        limit ${r"#{page.limit}"} offset ${r"#{page.offset}"}
    </select>
    <select id="findByPage" resultMap="BaseResultMap">
        select
        <include refid="sql_all_columns" ></include>
        from <include refid="sql_table_name"></include>
        <where>
            <include refid="sql_query_where"></include>
        </where>
        limit ${r"#{page.limit}"} offset ${r"#{page.offset}"}
    </select>
    <select id="findByPageWithOrderBy" resultMap="BaseResultMap">
        select
        <include refid="sql_all_columns" ></include>
        from <include refid="sql_table_name"></include>
        <where>
            <include refid="sql_query_where"></include>
            <if test="orderBy.stationaryList != null">
                <foreach collection="orderBy.stationaryList" item="item">
                    and ${r"${item.column} ${item.affirm} #{item.val}"}
                </foreach>
            </if>
        </where>
        <if test="orderBy != null">
            order by <foreach collection="orderBy.items" item="ob" separator=",">${r"${ob.column} ${ob.tag}"} </foreach>
        </if>
        limit ${r"#{page.limit}"} offset ${r"#{page.offset}"}
    </select>


    <!-- insert语句，如果存在唯一自增主键，则insert完成后自增值在主键字段中 -->
    <insert id="insert" >
        insert into <include refid="sql_table_name"></include>
        (<include refid="sql_all_columns_all"></include>)
        values
        (<include refid="sql_entity_insert"></include>)
    </insert>
    <!-- insert语句，如果存在唯一自增主键，则insert完成后自增值在主键字段中 -->
    <insert id="multipleInsert" >
        insert into <include refid="sql_table_name"></include>
        (<include refid="sql_all_columns_all"></include>)
        values
        <foreach collection="entities" item="entity" separator=",">
            (<include refid="sql_entity_insert"></include>)
        </foreach>
    </insert>


    <!-- update语句，返回收影响的行数 -->
    <update id="update" >
        update <include refid="sql_table_name"></include> set
        <include refid="sql_entity_update"></include>
        <where>
            <include refid="sql_query_where"></include>
        </where>
    </update>
    <!-- 批量update语句，返回收影响的行数 -->
    <update id="multipleUpdate">
        <foreach collection="updates" item="update" separator=";">
            update <include refid="sql_table_name"></include> set
            <include refid="sql_update_entity_update"></include>
            <where>
                <include refid="sql_update_entity_query"></include>
            </where>
        </foreach>
    </update>


    <!-- delete语句，返回收影响的行数 -->
    <delete id="delete">
        delete from <include refid="sql_table_name"></include>
        <where>
            <include refid="sql_query_where"></include>
        </where>
    </delete>


    <!-- 建表语句 -->
    <update id="createTable">
        ${m.t.createSql}
    </update>
    <!-- 所有分表 -->
    <select id="allTables" resultType="java.lang.String">
        ${m.t.allTablesSql}
    </select>

</mapper>